﻿using Mapster;
using StationDataHub.Models.Entity;

namespace StationDataHub.Services;

public interface IDbService
{
    /// <summary>
    /// 插入或更新数据
    /// </summary>
    Task<bool> InsertOrUpdateAsync(QueryStationsResponse response);

    /// <summary>
    /// 查询EV_StationInfo表的StationID集合
    /// </summary>
    Task<string[]> GetStationIDsAsync();

    /// <summary>
    /// 更新接口状态
    /// </summary>
    Task<bool> UpdateConnectorStatusAsync(QueryConnectorStatusResponse response);
}

public class DbService(ISqlSugarClient db) : IDbService
{
    private readonly ISqlSugarClient Db = db;

    /// <summary>
    /// 批量插入或更新充电站信息
    /// </summary>
    /// <param name="response"></param>
    /// <returns></returns>
    public async Task<bool> InsertOrUpdateAsync(QueryStationsResponse response)
    {
        if (response == null || response.StationInfos == null || response.StationInfos.Count == 0)
            return false;

        var stationList = new List<EV_StationInfo>();
        var equipmentList = new List<EV_EquipmentInfo>();
        var connectorList = new List<EV_ConnectorInfo>();

        foreach (var s in response.StationInfos)
        {
            // --- Station ---
            var stationEntity = s.Adapt<EV_StationInfo>();
            stationEntity.UpdateTime = DateTime.Now;
            // 总位总数（默认一个充电接口标识一个车位）
            stationEntity.ParkTotalNums = s.EquipmentInfos?.SelectMany(e => e.ConnectorInfos ?? []).Count() ?? 0;
            stationEntity.ParkNums = 0; // 可用车位数量手动置0，后续根据统计更新
            stationList.Add(stationEntity);

            // --- Equipment ---
            if (s.EquipmentInfos != null)
            {
                foreach (var e in s.EquipmentInfos)
                {
                    var equipEntity = e.Adapt<EV_EquipmentInfo>();
                    equipEntity.StationID = s.StationID;
                    equipEntity.UpdateTime = DateTime.Now;
                    equipmentList.Add(equipEntity);

                    // --- Connector ---
                    if (e.ConnectorInfos != null)
                    {
                        foreach (var c in e.ConnectorInfos)
                        {
                            var connEntity = c.Adapt<EV_ConnectorInfo>();
                            connEntity.StationID = s.StationID;
                            connEntity.EquipmentID = e.EquipmentID;
                            connectorList.Add(connEntity);
                        }
                    }
                }
            }
        }

        // Station 批量插入或更新
        var stationStorage = Db.Storageable(stationList)
            .WhereColumns(x => new { x.StationID })
            .ToStorage();
        await stationStorage.AsInsertable.ExecuteCommandAsync();
        //await stationStorage.AsUpdateable.IgnoreColumns(x => x.ParkNums).ExecuteCommandAsync();
        await stationStorage.AsUpdateable.ExecuteCommandAsync();

        // Equipment 批量插入或更新
        var equipStorage = Db.Storageable(equipmentList)
            .WhereColumns(x => new { x.StationID, x.EquipmentID })
            .ToStorage();
        await equipStorage.AsInsertable.ExecuteCommandAsync();
        await equipStorage.AsUpdateable.ExecuteCommandAsync();

        // Connector 批量插入或更新
        var connStorage = Db.Storageable(connectorList)
            .WhereColumns(x => new { x.StationID, x.EquipmentID, x.ConnectorID })
            .ToStorage();
        await connStorage.AsInsertable.ExecuteCommandAsync();
        await connStorage.AsUpdateable.ExecuteCommandAsync();

        return true;
    }

    /// <summary>
    /// 查询充电站表的StationID集合
    /// </summary>
    /// <returns></returns>
    public async Task<string[]> GetStationIDsAsync()
    {
        return await Db.Queryable<EV_StationInfo>().Select(x => x.StationID).ToArrayAsync();
    }

    /// <summary>
    /// 批量更新接口状态（仅更新 Status 不为空的记录）
    /// </summary>
    public async Task<bool> UpdateConnectorStatusAsync(QueryConnectorStatusResponse response)
    {
        if (response?.StationStatusInfos == null || response.StationStatusInfos.Length == 0)
            return false;
        // 1. 更新接口状态
        var updateList = response.StationStatusInfos
            .Where(s => s.ConnectorStatusInfos != null)
            .SelectMany(station => station.ConnectorStatusInfos
            .Where(c => !string.IsNullOrWhiteSpace(c.Status))
            .Select(c => new EV_ConnectorInfo
            {
                StationID = station.StationID,
                ConnectorID = c.ConnectorID,
                Status = int.TryParse(c.Status, out var s) ? s : null,
                UpdateTime = DateTime.Now
            }))
            .ToList();

        if (updateList.Count == 0)
        {
            Log.Information("没有可更新的接口状态");
            return false;
        }
        // 批量更新
        var result = await Db.Updateable(updateList)
            .UpdateColumns(x => new { x.Status, x.UpdateTime })
            .WhereColumns(x => new { x.StationID, x.ConnectorID })
            .ExecuteCommandAsync();
        Log.Information($"成功更新 {result} 条接口状态记录");

        // 2. 统计每个站点的空闲车位数量 (Status == 1)
        var parkStatusList = updateList
            .Where(x => x.Status == 1)
            .GroupBy(x => x.StationID)
            .Select(g => new
            {
                StationID = g.Key,
                ParkNums = g.Count()
            })
            .ToList();
        if (parkStatusList.Count == 0)
            return true;
        // 批量更新
        var updateDicts = parkStatusList.Select(x => new Dictionary<string, object>
        {
            ["StationID"] = x.StationID,
            ["ParkNums"] = x.ParkNums,
            ["UpdateTime"] = DateTime.Now
        }).ToList();

        // 一次性批量更新
        await Db.Updateable(updateDicts)
            .AS("EV_StationInfo")
            .UpdateColumns("ParkNums")
            .WhereColumns("StationID")
            .ExecuteCommandAsync();

        Log.Information($"成功更新 {updateDicts.Count} 个站点的车位空闲数");

        return result > 0;
    }
}